﻿using System.Collections.Generic;
using System.Data;
using System.Collections;
using System.Data.Common;
using System.Linq;
using System.Text;
using System;

namespace DBCore.DB.DAOS
{
    public class OracleDao:DAO
    {
        public static DBExecuter.DALSoapClient Excuter { get; set; }

        public OracleDao()
        {
            if (null == Excuter)
            {
                Excuter = new DBExecuter.DALSoapClient();
            }
        }
        //todo
      
        //done
        public override int Add(DbParam[] modelparam)
        {
            var colums=(from t in modelparam select t.Name).ToList();
            var values=(from t in modelparam select GetDetail(t)).ToList();
            string sql="insert into "+this.TableName+"("+colums.Join(",")+")"+"Values("+values.Join(",")+")";
            return Excuter.ExecuteNonQuery(ConnectString,sql);
        }


        public override int AddRange(List<DbParam[]> modelparam)
        {
            ArrayList al = new ArrayList();
            foreach (var item in modelparam)
            {
                var colums = (from t in item select t.Name).ToList();
                var values = (from t in item select GetDetail(t)).ToList();
                string sql = "insert into " + this.TableName + "(" + colums.Join(",") + ")" + "Values(" + values.Join(",") + ")";
                al.Add(sql);
            }
           return Excuter.ExecuteBatchNonQuery(ConnectString, al.ToArray(typeof(string)) as string[]);
        }
        /// <summary>
        /// done
        /// </summary>
        /// <param name="modelparam">每个Column的属性</param>
        /// <param name="pk"></param>
        /// <returns></returns>
        public override int Update(DbParam[] modelparam, DbParam pk)
        {
            var columns = (from m in modelparam where m.Name != pk.Name select "" + m.Name + "=" +GetDetail(m)).ToList();
            if (columns.Count > 0)
            {
                   var cmd = this.ConnectString;
                   string sql = "update " + this.TableName + " set " + columns.Join(",") + " where " + pk.Name + "=" + GetDetail(pk);
                   return Excuter.ExecuteNonQuery(cmd, sql);
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 通过什么删除
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public override int DeleteByWhere(SqlParam where)
        {
            string sql = "delete " + this.TableName + ((where == null || where.Text == null || where.Text.Trim() == "") ? "" : " where " + where.Text);
            return Excuter.ExecuteNonQuery(ConnectString, sql);//new SqlParam(sql, ((where == null || where.Parameters == null) ? null : where.Parameters)));
        }
        //done
        public override int DeleteByKey(DbParam pk)
        {
            return DeleteByWhere(new SqlParam(pk.Name + "=" +GetDetail(pk)));
        }
        //done
        public override bool Exists(SqlParam where)
        {
            string sql = "select count(*)  from " + this.TableName + ((where == null || where.Text == null || where.Text.Trim() == "") ? "" : " where " + where.Text);
            var count = Excuter.ExecuteScalar(ConnectString, sql);
            if (Convert.ToInt32(count) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
       /// <summary>
       /// 根据Where条件计数
       /// </summary>
       /// <param name="where"></param>
       /// <returns></returns>
        public override int Count(SqlParam where)
        {
            string sql = "select count(*)  from " + this.TableName + ((where == null || where.Text == null || where.Text.Trim() == "") ? "" : " where " + where.Text);
            var count = Excuter.ExecuteScalar(ConnectString, sql);
            return Convert.ToInt32(count);
        }
        //done
        public override DataSet GetDataSet(int topn, string columns, SqlParam where, string orderby)
        {
            string sqlinner = "select " + columns + " from " + this.TableName + ((where == null || where.Text == null || where.Text.Trim() == "") ? "" : " where " + where.Text) + (string.IsNullOrEmpty(orderby)? "" : "order by " + orderby);
            string sql = " select * from (" + sqlinner + ")" + (topn > 0 ? " where rownum<=" + topn : "");//大于零的情况下就查询前多少条数据
            return Excuter.ExecuteDataSet(ConnectString,sql);
        }
        //done
        public override DataSet GetDataSetByGroup(int topn, string columns, SqlParam where, string orderby,string groupby)
        {
            string sqlinner = "select " + columns + " from " + this.TableName + ((where == null || where.Text == null || where.Text.Trim() == "") ? "" : " where " + where.Text) + (string.IsNullOrEmpty(orderby) ? "" : " order by " + orderby)+(string.IsNullOrEmpty(groupby)?"":" group by "+groupby);
            string sql = " select * from (" + sqlinner + ")" + (topn > 0 ? " where rownum<=" + topn : "");//大于零的情况下就查询前多少条数据
            return Excuter.ExecuteDataSet(ConnectString, sql);
        }
        //默认主表是A
        //done
        public override DataSet GetJoinDataSet(int topn, string columns,SqlParam where, string orderby,string JoinTableName,string on)
        {
            string sqlinner = "select " + columns + " from " + this.TableName + " a " + (string.IsNullOrEmpty(JoinTableName) ? "" : " join ") + JoinTableName + " on " + on + ((where == null || where.Text == null || where.Text.Trim() == "") ? "" : " where " + where.Text) + (string.IsNullOrEmpty(orderby) ? "" : " order by " + orderby);
            string sql = " select * from (" + sqlinner + ")" + (topn > 0 ? "where rownum<=" + topn : "");//大于零的情况下就查询前多少条数据
            return Excuter.ExecuteDataSet(ConnectString, sql);
        }
        //done
        public override KeyValuePair<DataSet, int> GetPageDateSet(int page, int pagesize, SqlParam where, string columns, string orderby, string pk)
        {
            var f = (string.IsNullOrEmpty(columns) ? "*" : columns);
            var w = "";
            if (where != null && where.Text != null && where.Text.Trim() != "")
            {
                w = " where " + where.Text;
            }
            var o = "";
            if (orderby != null && orderby.Trim() != "")
            {
                o = " order by " + orderby;
            }
            var totalsql = "select count(0) count from " + this.TableName + " a " + (where != null && !string.IsNullOrEmpty(where.Text) ?w:"");
            var obj = Excuter.ExecuteScalar(ConnectString, totalsql);
            var total = Convert.ToInt32(obj);
            string sqlinner = "select  " + f + " from " + this.TableName + " a " + w + o;
            string sql = "select * from (select rownum rn,old.* from (" + sqlinner + ")old) where  rn>" + (page - 1) * pagesize + " and rn<=" + page * pagesize;
            return new System.Collections.Generic.KeyValuePair<DataSet, int>(Excuter.ExecuteDataSet(ConnectString, sql), total);//new SqlParam(sb.ToString(), where.Parameters)), total);
        }
        //done
        public override KeyValuePair<DataSet, int> GetJoinPageDateSet(int page, int pagesize, string columns,  SqlParam where, string ordreby,string tablename,string on)
        {
            string f = (string.IsNullOrEmpty(columns) ? "*" : columns);
            string w = "";
            if (where != null && where.Text != null && where.Text.Trim() != "")
            {
                w = " where " + where.Text;
            }
            string o = "";
            if (ordreby != null && ordreby.Trim() != "")
            {
                o = " order by " + ordreby;
            }
            //string sqlwhere = " from " + this.TableName + " a " + (string.IsNullOrEmpty(tablename) ? "" : "join ") + tablename + (string.IsNullOrEmpty(on) ? "" : " on " + on) + w;
            //string sqlinner = "select  " + f + sqlwhere + o;
            //int total = Convert.ToInt32(Excuter.ExecuteScalar(ConnectString, "select count(0) count " + sqlwhere));
            int total = Convert.ToInt32(Excuter.ExecuteScalar(ConnectString, "select count(0) count from " + this.TableName + " a " + (string.IsNullOrEmpty(tablename) ? "" : "join ") + tablename + (string.IsNullOrEmpty(on) ? "" : " on " + on) + (where != null && !string.IsNullOrEmpty(where.Text) ? w : "") + o));
            string sqlinner = "select  " + f + " from " + this.TableName + " a " +(string.IsNullOrEmpty(tablename)?"":"join ")+tablename + (string.IsNullOrEmpty(on)?"":" on "+on)+w + o;
            string sql = "select * from (" + sqlinner + ") where  rownum>" + (page - 1) * pagesize + " and rownum<=" + page * pagesize;
            return new System.Collections.Generic.KeyValuePair<DataSet, int>(Excuter.ExecuteDataSet(ConnectString,sql),total);//new SqlParam(sb.ToString(), where.Parameters)), total);
        }

        /// <summary>
        /// 获取Db的对应参数返回SQL值
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public override string GetDetail(DbParam db)
        {
            if (db.DataType == DbDataType.String)
            { 
               return "'"+db.Value+"'";
            }
            else if (db.DataType == DbDataType.BigInt)
            {
                return db.Value.ToString();
            }
            else if (db.DataType == DbDataType.DateTime)
            {
                return "to_date('"+db.Value.ToString()+"','YYYY/MM/DD AM HH:MI:SS')";
            }
            else if (db.DataType == DbDataType.Char)
            {
                return "'" + db.Value + "'";
            }
            else
            {
                return "";
            }
        }
    }
}
